import xlrd
import xlwt
import random
import os

def read_file():
    files = os.listdir()
    all_files = []
    for i in files:
        if i.endswith(('.xls','.xlsx')):
            all_files.append(i)
    data_all = []
    for i in all_files:
        book = xlrd.open_workbook(i)
        sh = book.sheets()[0]
        h = sh.nrows
        row_0 = sh.row_values(0)
        for a in range(1,h):
            count = {}
            rows = sh.row_values(a)
            for i in range(0,len(rows)):
                count[row_0[i]] = rows[i]
            data_all.append(count)
    return data_all



def write_file(data_all):
    wb = xlwt.Workbook()
    sh = wb.add_sheet('sheet1')
    title = []
    for key in data_all[0].keys():
        title.append(key)
    dyl = 0
    for i in title:
        sh.write(0,dyl,i)
        dyl += 1
    for a in range(0,len(data_all)):
        for b in range(0,len(title)):
            sh.write(a+1,b,data_all[a][title[b]])
    wb.save('all.xls')


if __name__ == "__main__":
    data_all = read_file()
    write_file(data_all)
